﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;

namespace NopiCommon
{
    /// <summary>
    /// 将HtmlTable导出为Execl
    /// </summary>
    public class HtmlTableExport
    {
        /// <summary>
        /// 调整列宽
        /// </summary>
        /// <param name="RowList">需要调整的行集合</param>
        /// <param name="AddCellIndex">列索引</param>
        /// <param name="CellWidth">需要添加的列宽</param>
        public static void SetCellWidth(List<E_Row> RowList, int AddCellIndex,int SumCellWidth)
        {
            foreach (E_Row eRow in RowList)
            {
                int MaxCellIndex=eRow.CellS.Max(p => p.CellIndex);
                int TempSumCellWidth = eRow.CellS.Sum(p => p.CellWidth);//获取当前行的列宽
                if (MaxCellIndex <= AddCellIndex)
                {
                    E_Cell eCell = eRow.CellS.Where(p => p.CellIndex == MaxCellIndex).First();
                    if (TempSumCellWidth < SumCellWidth)
                    {
                        eCell.CellWidth += SumCellWidth - TempSumCellWidth;
                    }
                }
                else
                {
                    foreach (E_Cell eCell in eRow.CellS)
                    {
                        if (eCell.CellIndex == AddCellIndex)
                        {
                            if (TempSumCellWidth < SumCellWidth)
                            {
                                eCell.CellWidth += SumCellWidth - TempSumCellWidth;
                            }
                        }
                    }
                    //将其他列向后推移
                    List<E_Cell> TempCellList = eRow.CellS.Where(p => p.CellIndex > AddCellIndex).ToList();
                    foreach (E_Cell eCell in TempCellList)
                    {
                        eCell.CellIndex += SumCellWidth - TempSumCellWidth;
                    }
                }
            }
        }

        /// <summary>
        /// 调整列高
        /// </summary>
        /// <param name="eRow">需要调整的行</param>
        /// <param name="AddCellHeight">需要添加的高</param>
        public static void SetCellHeight(List<E_Row> RowList,E_Row AddRow, int AddCellHeight)
        {
            foreach (E_Cell eCell in AddRow.CellS)
            {
                eCell.CellHeight += AddCellHeight;
            }

            //将其他行向下推移
            List<E_Row> TempRowList = RowList.Where(p => p.RowIndex > AddRow.RowIndex).ToList();
            foreach (E_Row eRow in TempRowList)
            {
                eRow.RowIndex += AddCellHeight;
            }
        }

        /// <summary>
        /// DataTable转换成Excel文档流
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static MemoryStream RenderToExcel(string HtmlTable)
        {
            List<E_Row> RowList = JsonHelper.JsonDeserialize<List<E_Row>>(HtmlTable);

            //处理嵌套表格
            foreach (E_Row eRow in RowList)
            {
                foreach (E_Cell eCell in eRow.CellS)
                {
                    if (eCell.RowS != null && eCell.RowS.Count > 0)
                    {
                        //嵌套表格的列宽
                        int AddcolspanCount = eCell.RowS.First().CellS.Sum(p => p.CellWidth);
                        //嵌套表格的行高
                        int AddrowspanCount = eCell.RowS.Count;

                        SetCellWidth(RowList, eCell.CellIndex, eRow.CellS.Sum(p => p.CellWidth) + AddcolspanCount - eCell.CellWidth);

                        if (eCell.CellHeight < AddrowspanCount) //根据嵌套的表格行数补充当前行的列高
                        {
                            SetCellHeight(RowList, eRow, AddrowspanCount - eCell.CellHeight);
                        }
                    }
                }
            }

            string strjson = JsonHelper.JsonSerializer<List<E_Row>>(RowList);

            MemoryStream ms = new MemoryStream();
            using (IWorkbook workbook = new HSSFWorkbook())
            {
                using (ISheet sheet = workbook.CreateSheet())
                {
                    DataBindRowList(sheet, RowList, 0, 0); //绑定Execl
                    AutoSizeColumns(sheet);
                    workbook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;
                }
            }
            return ms;
        }

        /// <summary>
        /// 绑定行集合
        /// </summary>
        /// <param name="sheet">Execl工作目录</param>
        /// <param name="RowList">需要绑定的行集合</param>
        /// <param name="StartRowIndex">绑定的行起始位置</param>
        /// <param name="StartCellIndex">绑定的列起始维护</param>
        public static void DataBindRowList(ISheet sheet,List<E_Row> RowList,int StartRowIndex,int StartCellIndex)
        {
            foreach (E_Row eRow in RowList)
            {
                //获取可编辑的行
                IRow SheetRow = sheet.GetRow(eRow.RowIndex + StartRowIndex);
                if (SheetRow == null)
                {
                    SheetRow = sheet.CreateRow(eRow.RowIndex + StartRowIndex);
                }

                foreach (E_Cell eCell in eRow.CellS)
                {
                    if (eCell.RowS != null && eCell.RowS.Count > 0) //判断是否存在嵌套表格
                    {
                        //绑定嵌套表格内容
                        int TempStartRowIndex = eRow.RowIndex;      //起始行索引
                        int TempStartCellIndex = eCell.CellIndex;   //起始列索引
                        DataBindRowList(sheet, eCell.RowS, TempStartRowIndex, TempStartCellIndex);
                    }
                    else
                    {
                        //判断合并单元格
                        if (eCell.CellWidth > 1 || eCell.CellHeight > 1)
                        {
                            int RowStart = eRow.RowIndex + StartRowIndex;
                            int RowEnd = eRow.RowIndex + StartRowIndex + eCell.CellHeight - 1;
                            int CellStart = eCell.CellIndex + StartCellIndex;
                            int CellEnd = eCell.CellIndex + StartCellIndex + eCell.CellWidth - 1;
                            CellRangeAddress CellRangeAddress = new CellRangeAddress(RowStart, RowEnd, CellStart, CellEnd);
                            sheet.AddMergedRegion(CellRangeAddress);
                        }
                        SheetRow.CreateCell(eCell.CellIndex + StartCellIndex).SetCellValue(eCell.Content);
                    }
                }
            }
        }


        /// <summary>
        /// 自动设置Excel列宽
        /// </summary>
        /// <param name="sheet">Excel表</param>
        private static void AutoSizeColumns(ISheet sheet)
        {
            if (sheet.PhysicalNumberOfRows > 0)
            {
                IRow headerRow = sheet.GetRow(0);

                for (int i = 0, l = headerRow.LastCellNum; i < l; i++)
                {
                    sheet.AutoSizeColumn(i);
                }
            }
        }

        /// <summary>
        /// 保存Excel文档流到文件
        /// </summary>
        /// <param name="ms">Excel文档流</param>
        /// <param name="fileName">文件名</param>
        private static void SaveToFile(MemoryStream ms, string fileName)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();

                fs.Write(data, 0, data.Length);
                fs.Flush();

                data = null;
            }
        }


        /// <summary>
        /// DataTable转换成Excel文档流，并保存到文件
        /// </summary>
        /// <param name="table"></param>
        /// <param name="fileName">保存的路径</param>
        public static void RenderToExcel(string HtmlTable, string fileName)
        {
            using (MemoryStream ms = RenderToExcel(HtmlTable))
            {
                SaveToFile(ms, fileName);
            }
        }
    }
}
